package org.lyl.taobao.dao.impl;

import org.lyl.taobao.api.entity.Commodity;
import org.lyl.taobao.api.utils.DruidConnectionUtils;
import org.lyl.taobao.dao.ICommodityDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: Re
 * @Date: 2021/4/2 17:10
 */
public class CommodityDaoImpl implements ICommodityDao {
    DruidConnectionUtils druidConnectionUtils=new DruidConnectionUtils();
    @Override
    public boolean insertCommodity(Connection connection,Commodity commodity) {
        boolean flag = false;
        String sql = "insert into commodity(c_Name,c_Kind,c_Price,c_UserId) values(?,?,?,?);";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,commodity.getCommodityName());
            preparedStatement.setString(2,commodity.getCommodityKind());
            preparedStatement.setFloat(3,commodity.getCommodityPrice());
            preparedStatement.setInt(4,commodity.getCommodityUserId());
            int count = preparedStatement.executeUpdate();
            if (count == 1){
                flag = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            druidConnectionUtils.closeConnection(connection,preparedStatement,null);
        }
        return flag;
    }

    @Override
    public List<Commodity> checkAllCommodity(Connection connection) {
        List<Commodity> commodityList = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from Commodity;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int c_Id = resultSet.getInt("c_Id");
                String c_Name = resultSet.getString("c_Name");
                String c_Kind = resultSet.getString("c_Kind");
                float c_Price = resultSet.getFloat("c_Price");
                commodityList.add(new Commodity(c_Id,c_Name,c_Kind,c_Price));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            druidConnectionUtils.closeConnection(connection,preparedStatement,resultSet);
        }
        return commodityList;
    }

    @Override
    public Commodity checkCommodityByC_Id(Connection connection, Integer c_Id) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from commodity where c_Id = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,c_Id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Commodity commodity = new Commodity(c_Id,resultSet.getString("c_Name"), resultSet.getString("c_Kind"), resultSet.getFloat("c_Price"), resultSet.getInt("c_UserId"));
                return commodity;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Commodity> checkCommodityById(Connection connection,Integer UserId) {
        List<Commodity> commodityList = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from commodity where c_UserId = ? ;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,UserId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int c_Id = resultSet.getInt("c_Id");
                String c_Name = resultSet.getString("c_Name");
                String c_Kind = resultSet.getString("c_Kind");
                float c_Price = resultSet.getFloat("c_Price");
                commodityList.add(new Commodity(c_Id,c_Name,c_Kind,c_Price));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            druidConnectionUtils.closeConnection(connection,preparedStatement,resultSet);
        }
        return commodityList;
    }

    @Override
    public boolean deleteCommodity(Connection connection, int c_id) {
        boolean flag = false;
        PreparedStatement preparedStatement = null;
        String sql = "delete from commodity where c_Id = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,c_id);
            if (preparedStatement.executeUpdate() == 1){
                flag = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            druidConnectionUtils.closeConnection(connection,preparedStatement,null);
        }
        return flag;
    }

    @Override
    public boolean updateCommodity(Connection connection, int c_Id, String c_Name, float c_Price) {
        String sql = "update commodity set c_Name = ?,c_Price = ? where c_Id = ? ;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,c_Name);
            preparedStatement.setFloat(2,c_Price);
            preparedStatement.setInt(3,c_Id);
            if(preparedStatement.executeUpdate() == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            druidConnectionUtils.closeConnection(connection,preparedStatement,null);
        }
        return false;
    }

    @Override
    public float getCommodityPriceByC_Id(Connection connection, Integer c_Id) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select c_Price from commodity where c_Id = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,c_Id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                float price = resultSet.getFloat("c_Price");
                return price;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
